package mumber

import org.apache.kafka.clients.producer.{KafkaProducer, ProducerRecord}
import org.apache.kafka.common.serialization.{StringDeserializer, StringSerializer}
import org.apache.spark.SparkConf
import org.apache.spark.streaming.kafka010.LocationStrategies.PreferConsistent
import org.apache.spark.streaming.kafka010.{ConsumerStrategies, KafkaUtils}
import org.apache.spark.streaming.{Durations, StreamingContext}

import java.sql.DriverManager
import scala.collection.mutable

object state_spark_sql_yangmingru {

  case class personObj(state: String, gender: String, count: Int)

  def main(args: Array[String]): Unit = {
    // 设置Spark配置
    val conf = new SparkConf().setAppName("CombinedSparkKafkaMySQL").setMaster("local[*]")
    // 创建StreamingContext，设置批处理间隔为2秒
    val ssc = new StreamingContext(conf, Durations.seconds(2))
    ssc.sparkContext.setLogLevel("WARN")

    // Kafka相关配置参数（用于消费）
    val kafkaParams = mutable.Map[String, Object]()
    kafkaParams += ("bootstrap.servers" -> "192.168.136.128:9092")
    kafkaParams += ("key.deserializer" -> classOf[StringDeserializer])
    kafkaParams += ("value.deserializer" -> classOf[StringDeserializer])
    kafkaParams += ("group.id" -> "combined - niit")
    kafkaParams += ("auto.offset.reset" -> "earliest")

    // 要消费的Kafka主题列表
    val topics = Array("stuInfo")

    // 创建Kafka DStream，直接读取数据
    val kafkaStream = KafkaUtils.createDirectStream[String, String](
      ssc,
      PreferConsistent,
      ConsumerStrategies.Subscribe[String, String](topics, kafkaParams)
    ).map(record => record.value())

    // 从Kafka消息中提取状态和性别信息，转换为((状态, 性别), 1)的格式
    val stateGenderCountStream = kafkaStream.flatMap(line => {
      val fields = line.split("\t")
      val state = fields(6)
      val gender = if (fields(2).toInt == 0) "female" else "male"
      Seq(((state, gender), 1))
    })

    // 按状态和性别进行分组，并统计人数总和
    val totalCountStream = stateGenderCountStream.reduceByKey(_ + _)

    // Kafka相关配置（用于生产）
    val kafkaParamsForProduce = new java.util.HashMap[String, Object]()
    kafkaParamsForProduce.put("bootstrap.servers", "192.168.136.128:9092")
    kafkaParamsForProduce.put("key.serializer", classOf[StringSerializer])
    kafkaParamsForProduce.put("value.serializer", classOf[StringSerializer])

    // 将统计结果发送到指定的Kafka主题
    totalCountStream.foreachRDD(rdd => {
      if (!rdd.isEmpty()) {
        rdd.foreach { case ((state, gender), count) =>
          // 在每个执行节点上创建KafkaProducer对象
          val producer = new KafkaProducer[String, String](kafkaParamsForProduce)
          val record = new ProducerRecord[String, String]("stateStu", s"$state,$gender,$count")
          producer.send(record)
          producer.close()
        }
      }
    }
    )
    // 配置Kafka消费者参数，用于从新的stu读取数据
    val kakaParamsForMySQL = Map[String, Object](
      "bootstrap.servers" -> "192.168.136.128:9092",
      "key.deserializer" -> classOf[StringDeserializer],
      "value.deserializer" -> classOf[StringDeserializer],
      "group.id" -> "combined - niit",
      "enable.auto.commit" -> (false: java.lang.Boolean)
    )

    // 要读取的主题名称
    val topicNameForMySQL = Array("stateStu")

    // 从Kafka获取数据流
    val streamRdd = KafkaUtils.createDirectStream[String, String](
      ssc, PreferConsistent,
      ConsumerStrategies.Subscribe[String, String](topicNameForMySQL, kakaParamsForMySQL)
    )

    val rowRdd = streamRdd.map(_.value()).map(line => {
      val fields = line.split(",")
      personObj(fields(0), fields(1), fields(2).toInt)
    })

    // 每隔2秒对数据进行统计处理
    rowRdd.foreachRDD(rdd => {
      // 按学期和性别分组并计算每组的数量总和
      val genderCount = rdd.map(person => ((person.state, person.gender), person.count))
        .reduceByKey(_ + _)

      // 打印当前批次的统计结果（可用于调试查看数据）
      genderCount.foreach(println)

      // 将统计结果推送到MySQL数据库
      genderCount.foreachPartition(partition => {
        val connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/HUEL?characterEncoding=UTF-8&useSSL=false", "root", "123456")
        val statement = connection.createStatement()

        partition.foreach { case ((state, gender), count) =>
          val sql = s"INSERT INTO teststate (state, gender, count) VALUES ('$state', '$gender', $count)"
          statement.executeUpdate(sql)
        }

        statement.close()
        connection.close()
      })
    })

    // 启动流式处理并等待终止
    ssc.start()
    ssc.awaitTermination()
  }
}
